using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CrudI.Interface;
using CrudCreator.Creators;
using SQLCodeCreator.DBInfo.SqlServer;
using System.Data.SqlClient;
//using Microsoft.DirectX;
//using Microsoft.DirectX.Direct3D;

namespace SQLCodeCreator
{
    public partial class FrmMain : Form
    {        
        private SqlDatabase originalTables;
        private List<CodeStore> cSharpCodeStoreList = new List<CodeStore>();
        private List<CodeStore> insertCodeStoreList = new List<CodeStore>();
        private List<CodeStore> updateCodeStoreList = new List<CodeStore>();
        private List<CodeStore> selectCodeStoreList = new List<CodeStore>();
        private List<CodeStore> selectByIdStoreList = new List<CodeStore>();
        
        public FrmMain()
        {
            InitializeComponent();
        }

        private void BtnConnecToServer_Click(object sender, EventArgs e)
        {
            SetFormVisible(false);
            BtnConnecToServer.Enabled = false;
            string butText = BtnConnecToServer.Text;
            BtnConnecToServer.Text = "Loading Tables";
            Application.DoEvents();
            if (TxtConnectionBox.Text.Trim().Length == 0)
            {
                MessageBox.Show("Connection string not valid - it's blank!!");                
                BtnConnecToServer.Text = butText;
                BtnConnecToServer.Enabled = true;
                return;
            }

            SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder();
            conStringBuilder.ConnectionString = TxtConnectionBox.Text;

            SqlConnection con = new SqlConnection(conStringBuilder.ConnectionString);
            try
            {
                con.Open();
                con.Close();
                GlobaVars.connectionValid = true;
                GlobaVars.sqlConnection = conStringBuilder;
            }
            catch (Exception ex)
            {
                GlobaVars.connectionValid = false;
                MessageBox.Show(ex.Message);
            }

            if (!GlobaVars.connectionValid)
            {
                MessageBox.Show("Connection not valid");
                BtnConnecToServer.Text = butText;
                BtnConnecToServer.Enabled = true;
                return;
            }
            if (GlobaVars.connectionValid)
            {                
                originalTables = new SqlDatabase();
                originalTables.DatabaseName = GlobaVars.sqlConnection.InitialCatalog;
                LstTables.Items.Clear();
                originalTables.TableList = SqlDatabase.GetTables(GlobaVars.sqlConnection.ToString());
                LstChosenTables.Items.Clear();
                for (int i = 0; i < originalTables.TableList.Count; i++)
                {
                    LstTables.Items.Add(originalTables.TableList[i].TableName);
                }
               
                SetFormVisible(true);
                BtnConnecToServer.Text = butText;
                BtnConnecToServer.Enabled = true;
                MessageBox.Show("Double click the table names in the \"Table names\" list to add to your create list");
            }
        }

        private void SetFormVisible(bool visible)
        {
            LstTables.Visible = visible;
            LstChosenTables.Visible = visible;
            BtnTablesReset.Visible = visible;
            //BtnCreateCode.Visible = visible;
            tabControl1.Visible = visible;
            GbCheckboxes.Visible = visible;
            LblTableText.Visible = visible;
            LblAddedTableText.Visible = visible;
            ChkUseAutoProperties.Visible = visible;
        }       

        private void CreateCode(List<ITable> tablesCode)
        {           
            RtbClass.Clear();
            cSharpCodeStoreList.Clear();
            insertCodeStoreList.Clear();
            updateCodeStoreList.Clear();
            selectCodeStoreList.Clear();
            selectByIdStoreList.Clear();
            for (int i = 0; i < tablesCode.Count; i++)
                tablesCode[i].AddColumns();           

            StringBuilder sbCode = new StringBuilder();
            StringBuilder sbSql = new StringBuilder();
            RtbSummary.Clear();
            RtbProcedures.Clear();
            RtbClass.Clear();

            char lineChar = '*';
            string lineBreak = new string(lineChar, 40);

            //string procedurePrefix = TxtProcedurePrefix.Text;
            string procedurePrefix = "@";

            CodeStore codeStore;

            // go through chosen tables
            for (int i = 0; i < tablesCode.Count; i++)
            {
                codeStore = new CodeStore();
                if (ChkCreateClass.Checked)
                {
                    codeStore.TableName = tablesCode[i].TableName;
                    codeStore.Code = ClassObjects.GetCsharpBusinessObject(tablesCode[i], TxtDefaultNamespace.Text.Trim(), ChkUseAutoProperties.Checked);

                    sbCode.AppendLine(codeStore.Code + Environment.NewLine);
                    cSharpCodeStoreList.Add(codeStore);
                }
                if (ChkCreateInsert.Checked)
                {
                    codeStore = new CodeStore();
                    codeStore.TableName = tablesCode[i].TableName;
                    codeStore.Code = CreateSqlInsertProcedure.GetInsertProcedure(tablesCode[i], originalTables.DatabaseName, procedurePrefix);
                    sbSql.AppendLine(codeStore.Code);
                    insertCodeStoreList.Add(codeStore);
                }
                if (ChkCreateUpdate.Checked)
                {
                    codeStore = new CodeStore();
                    codeStore.TableName = tablesCode[i].TableName;
                    codeStore.Code = CreateSqlUpdateProcedure.GetUpdateProcedure(tablesCode[i], originalTables.DatabaseName, procedurePrefix);
                    sbSql.AppendLine(codeStore.Code);
                    updateCodeStoreList.Add(codeStore);
                }
                if (ChkCreateSelect.Checked)
                {
                    codeStore = new CodeStore();
                    codeStore.TableName = tablesCode[i].TableName;
                    codeStore.Code = CreateSqlSelectProcedure.GetSelectProcedure(tablesCode[i], originalTables.DatabaseName, procedurePrefix);
                    sbSql.AppendLine(codeStore.Code);
                    selectCodeStoreList.Add(codeStore);
                }
                if (ChkSelectById.Checked)
                {
                    codeStore = new CodeStore();
                    codeStore.TableName = tablesCode[i].TableName;
                    codeStore.Code = CreateSqlSelectById.GetSelectByIdProcedure(tablesCode[i], originalTables.DatabaseName, procedurePrefix);
                    sbSql.AppendLine(codeStore.Code);
                    selectByIdStoreList.Add(codeStore);
                }
            }

            if (tablesCode.Count == 0)
            {
                TpProcedures.Text = "Procedures (" + tablesCode.Count + ")";
                TpClasses.Text = "Classes (" + tablesCode.Count + ")"; 
                RtbSummary.Clear();
                RtbSummary.Text = "No Tables chosen, double click a table in the table list to move into the chosen tables list";
                
            }
            else
            {
                TpProcedures.Text = "Procedures ( " + (insertCodeStoreList.Count + selectCodeStoreList.Count + updateCodeStoreList.Count) + " )";
                TpClasses.Text = "Class ( " + cSharpCodeStoreList.Count + " )";
                RtbClass.Text = sbCode.ToString() + Environment.NewLine + Environment.NewLine;
                RtbProcedures.Text = sbSql.ToString() + Environment.NewLine + Environment.NewLine;
                RtbClass.Visible = true;
                RtbClass.Enabled = true;

                string[] classLines = RtbClass.Lines;
                string[] procLines = RtbProcedures.Lines;
                
                //EmptyMessageLabel();
               
                RtbSummary.Text += " Code Summary" + Environment.NewLine + Environment.NewLine +
                    " Classes Created " + cSharpCodeStoreList.Count + Environment.NewLine +
                    " Stored procedures created " + (insertCodeStoreList.Count + selectCodeStoreList.Count + updateCodeStoreList.Count) + Environment.NewLine +
                    " Number of lines written " + (classLines.Length + procLines.Length) + Environment.NewLine;
            }
        }

        private void LstTables_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            List<ITable> tables = new List<ITable>();
            string tableName = LstTables.SelectedItem.ToString();
            LstTables.Items.Remove(tableName);
            // needs to be in an ordered list            

            List<string> sortedTables = new List<string>();
            List<string> unsortedTables = new List<string>();

            // get a list of all tables needed for the chosen tables
            unsortedTables.Add(tableName);
            for (int i = 0; i < LstChosenTables.Items.Count; i++)
            {
                unsortedTables.Add(LstChosenTables.Items[i].ToString());
            }
            
            //go through the full ordered list of tables and create an order list of chosen tables from the unsorted list
            for (int i = 0; i < originalTables.TableList.Count; i++)
            {
                for (int j = 0; j < unsortedTables.Count; j++)
                {
                    if (originalTables.TableList[i].TableName == unsortedTables[j].ToString())
                    {                       
                        sortedTables.Add(unsortedTables[j].ToString());
                    }
                }
            }

            ITable table;
            LstChosenTables.Items.Clear();
            for (int s = 0; s < sortedTables.Count; s++)
            {
                LstChosenTables.Items.Add(sortedTables[s].ToString());
                table = new SqlTable(sortedTables[s].ToString());
                tables.Add(table);
            }

            CreateCode(tables);
        }

        private void LstColumns_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (LstChosenTables.SelectedItem != null)
            {
                string tableName = LstChosenTables.SelectedItem.ToString();
                LstChosenTables.Items.Remove(tableName);

                // go through all tablenames and create a new string list array but dont add the ones that are in this listbox
                // we are re-making the list so all tables are ordered by name (as apose to just putting the table name back)
                bool tableFound = false;
                List<string> tableNamesToGoBack = new List<string>();
                for (int i = 0; i < originalTables.TableList.Count; i++)
                {
                    for (int j = 0; j < LstChosenTables.Items.Count; j++)
                    {
                        if (originalTables.TableList[i].TableName == LstChosenTables.Items[j].ToString())
                        {
                            tableFound = true;
                            break;
                        }
                    }
                    // build a new list removing any tables which are still in the tables picked listbox
                    if (tableFound)// if table found, dont add to the list
                        tableFound = false;
                    else tableNamesToGoBack.Add(originalTables.TableList[i].TableName);
                }
                // clear and reload all tablenames in LstTables ()
                LstTables.Items.Clear();

                for (int i = 0; i < tableNamesToGoBack.Count; i++)
                {
                    LstTables.Items.Add(tableNamesToGoBack[i]);
                }
            }
        }

        private void BtnCreateCode_Click(object sender, EventArgs e)
        {
            //CreateCode();
        }

        private void BtnRemoveAllChosenTables_Click(object sender, EventArgs e)
        {
            LstTables.Items.Clear();
            for (int i = 0; i < originalTables.TableList.Count; i++)
            {
                LstTables.Items.Add(originalTables.TableList[i].TableName);
            }
            LstChosenTables.Items.Clear();
            RtbClass.Clear();
            RtbProcedures.Clear();
            cSharpCodeStoreList.Clear();
            insertCodeStoreList.Clear();
            updateCodeStoreList.Clear();
            selectCodeStoreList.Clear();
            selectByIdStoreList.Clear();
            TpProcedures.Text = "Procedures (0)";
            TpClasses.Text = "Classes (0)";
            RtbSummary.Clear();
            RtbSummary.Text = "No Tables chosen, double click a table in the table list to move into the chosen tables list";

        }

        private void FrmMain_Shown(object sender, EventArgs e)
        {
            TxtConnectionBox.TabIndex = 0;
            TxtConnectionBox.TabStop = true;
            SetFormVisible(false);
            MessageBox.Show("Add your sql connection to get started.");
            TxtConnectionBox. SelectAll();
        }
    }
}